﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace AccessCommon
{
    public sealed class SqlDB : IDisposable
    {
        private string _constring;
        private SqlConnection _connection;
        private SqlTransaction _tran;
        private bool bConnectionKeepAlive;



        public SqlDB()
        {
            _constring = string.Empty;
        }

        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="constr">数据库连接字符串</param>
        /// <param name="bAutoOpen">是否打开连接</param>
        public SqlDB(string constr,bool bAutoOpen)
        {
            _constring = constr;
            this.bConnectionKeepAlive = bAutoOpen;
            if (this.IsConnectionKeepAlive())
            {
                this.OpenConnection();
            }

        }

        #region 数据库连接处理
        /// <summary>
        /// 创建并打开数据库连接
        /// </summary>
        /// <returns></returns>
        public SqlConnection OpenConnection()
        {
            if (this._constring == string.Empty)
            {
                return null;
            }
            if (this._connection == null)
            {
                this._connection = new SqlConnection();
            }
            if (this._connection.State != ConnectionState.Open)
            {
                try
                {
                    this._connection = new SqlConnection(this._constring);
                    this._connection.Open();
                }
                catch (Exception exception)
                {
                    throw exception;
                }
            }
            return this._connection;

        }

        /// <summary>
        /// 关闭数据库
        /// </summary>
        /// <returns></returns>
        public bool CloseConnection()
        {
            try
            {
                if (this._connection != null && _connection.State != ConnectionState.Closed)
                {
                    this._connection.Close();
                }
                return true;
            }
            catch
            {
                return false;
            }

        }

        public bool IsConnectionAlive()
        {
            try
            {
                if (this._connection.State == ConnectionState.Open)
                {
                    return true;
                }
            }
            catch
            {
            }
            return false;
        }

        public bool IsConnectionKeepAlive()
        {
            return this.bConnectionKeepAlive;
        }



        #endregion 

        #region 事务处理

        public SqlTransaction BeginTransaction()
        {
            this.OpenConnection();
            this._tran = this._connection.BeginTransaction();
            return this._tran;
        }

        public bool Commit()
        {
            if (!this.IsOpenTransaction())
            {
                return false;
            }
            this._tran.Commit();
            return true;
        }

        public bool EndTransaction()
        {
            if (!this.IsConnectionAlive())
            {
                return false;
            }
            if (!this.IsConnectionKeepAlive())
            {
                this.CloseConnection();
            }
            this._tran = null;
            return true;
        }

        public bool IsOpenTransaction()
        {
            if (this._tran == null)
            {
                return false;
            }
            return true;
        }


        public bool Rollback()
        {
            if (!this.IsOpenTransaction())
            {
                return false;
            }
            this._tran.Rollback();
            return true;
        }


 



        #endregion

        #region ADO.NET SQL 操作

        public DataSet ExecuteDataset(CommandType cmdType, string cmdText)
        {
            return ExecuteDataset(cmdType, cmdText, null);
        }


        public DataSet ExecuteDataset(CommandType cmdType, string cmdText, params SqlParameter[] cmdParams)
        {
            this.OpenConnection();
            
                DataSet dataSet = null;
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    PrepareCommand(this.Connection, this._tran, cmd, cmdType, cmdText, cmdParams);
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    cmd.Parameters.Clear();
                }
                catch (Exception exception)
                {
                    throw exception;
                }
                finally
                {
                    if (!this.IsConnectionKeepAlive() && !this.IsOpenTransaction())
                    {
                        this.CloseConnection();
                    }
                }
               
                return dataSet;
            
           
        }

        public int ExecuteNonQuery(CommandType cmdType, string cmdText)
        {
            return this.ExecuteNonQuery(cmdType, cmdText, null);
        }


        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] cmdParams)
        {
            this.OpenConnection();
            int num = 0;
            try
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(this.Connection, this.Transaction, cmd, cmdType, cmdText, cmdParams);
                num = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
            catch (Exception exception)
            {               
                throw exception;
            }
            finally
            {
                if (!this.IsConnectionKeepAlive() && !this.IsOpenTransaction())
                {
                    this.CloseConnection();
                }
            }
           
            return num;
        }


        public SqlDataReader ExecuteReader(CommandType cmdType, string cmdText)
        {
            return this.ExecuteReader(cmdType, cmdText, null);
        }

        public SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] cmdParams)
        {
            this.OpenConnection();
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(this.Connection, this.Transaction, cmd, cmdType, cmdText, cmdParams);
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
           
            return reader;
        }


        public object ExecuteScalar(CommandType cmdType, string cmdText)
        {
            return this.ExecuteScalar(cmdType, cmdText, null);
        }

        public object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] cmdParams)
        {
            this.OpenConnection();
            object obj2 = null;
            try
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(this.Connection, this.Transaction, cmd, cmdType, cmdText, cmdParams);
                obj2 = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
            }
            catch (Exception exception)
            {
               
                throw exception;
            }
            finally
            {
                if (!this.IsConnectionKeepAlive() && !this.IsOpenTransaction())
                {
                    this.CloseConnection();
                }
            }
            
            return obj2;
        }


       /// <summary>
        /// 分页操作 2009-09-23
       /// </summary>
       /// <param name="strSelSrc">查询数据源</param>
        /// <param name="strOrder">分页排序字段</param>
       /// <param name="strShowCol">查询字段</param>       
       /// <param name="intPageSize">分页大小</param>
       /// <param name="intCurrentIndex">当前页</param>
       /// <param name="Total">总数</param>
       /// <returns></returns>
        public DataTable GetPrintTable(string strSelSrc, string strOrder, string strShowCol,  int intPageSize, int intCurrentIndex, out int Total)
        {
            string strCount = "select count(*) as Total from (" + strSelSrc + ") as z ";

            string strsql = GetPageSelString(strSelSrc, strOrder, strShowCol,  intPageSize, intCurrentIndex);
            
            Total = 0;
        
            try
            {                  
                Total = Convert.ToInt32(this.ExecuteScalar(CommandType.Text,strCount) );

                DataTable dt = this.ExecuteDataset(CommandType.Text, strsql).Tables[0];
                return dt;
            }
            catch
            {
                return null;
            }

        }

        /// <summary>
        /// 获取分页查询语句 SQL 2005 专用 2009-09-23
        /// </summary>
        /// <param name="strSelSrc"></param>
        /// <param name="strExclID"></param>
        /// <param name="strShowCol"></param>
        /// <param name="strOrder"></param>
        /// <param name="intPageSize"></param>
        /// <param name="intCurrentIndex"></param>
        /// <returns></returns>
        public string GetPageSelString(string strSelSrc, string strOrder, string strShowCol,  int intPageSize, int intCurrentIndex)
        {
           // select * from (select row_number() over (order by CheckID) row,* from (select * from CheckInfo) a)z
            //where row between 0 and 100


            StringBuilder builder = new StringBuilder();

            builder.AppendFormat("select {0} from ( select row_number() over (order by {1} ) row,* from ({2}) a)z ",
                new object[] { strShowCol, strOrder, strSelSrc });

            builder.AppendFormat(" where row between {0}*({1}-1)+1 and {0}*{1} ", new object[] { intPageSize, intCurrentIndex });
           
            return builder.ToString();
        }

        #endregion


        private static void PrepareCommand(SqlConnection conn, SqlTransaction trans, SqlCommand cmd, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    cmd.Parameters.Add(parameter);
                }
            }
        }


        /// <summary>
        /// 释放资源
        /// </summary>
         public void Dispose()
        {
            this.CloseConnection();
            GC.SuppressFinalize(this);
        }


        public SqlConnection Connection
        {
            get { return _connection; }
            set {_connection=value;}
        }

        public SqlTransaction Transaction
        {
            get
            {
                return this._tran;
            }
            set
            {
                this._tran = value;
            }
        }



    }
}
